﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.OleDb;
using StockAdmin.Dao;

namespace StockAdmin.Controllers
{
    public class ImportController : Controller
    {
        //
        // GET: /Import/
        FinancialDao dao = new FinancialDao();

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Import()
        {
            return View();
        }

        public ActionResult Importexcel()
        {


            DataSet ds = new DataSet();
            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string fileExtension =
                                     System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload1"].FileName;

                    //if (System.IO.File.Exists(fileLocation))
                    //{
                    //    System.IO.File.Delete(fileLocation);
                    //}

                    Request.Files["FileUpload1"].SaveAs(fileLocation);

                    string excelConnectionString = string.Empty;
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();
                    DataTable dt = new DataTable();

                    dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return null;
                    }
                    String[] excelSheets = new String[dt.Rows.Count];
                    int t = 0;
                    //excel data saves in temp file here.
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[t] = row["TABLE_NAME"].ToString();
                        t++;
                    }
                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                    string query = string.Format("Select * from [{0}]", "Sheet1$");
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                    {
                        dataAdapter.Fill(ds);
                    }
                }

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String codeStock = ds.Tables[0].Rows[i][0].ToString();
                    String value = ds.Tables[0].Rows[i][1].ToString();
                    String qarter = ds.Tables[0].Rows[i][2].ToString();
                    String year = ds.Tables[0].Rows[i][3].ToString();
                    String type = ds.Tables[0].Rows[i][4].ToString();

                    String abc = dao.Select(codeStock);
                    if (!"".Equals(abc)) {
                        if ("".Equals(value))
                        {
                            value = "0";
                        }
                        dao.Insert(abc, value, qarter, year, type);

                        Console.WriteLine("insert ok for " + i + " of ds.Tables[0].Rows.Count");
                    }
                
                }

                


                //if (fileExtension.ToString().ToLower().Equals(".xml"))
                //{
                //    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
                //    if (System.IO.File.Exists(fileLocation))
                //    {
                //        System.IO.File.Delete(fileLocation);
                //    }

                //    Request.Files["FileUpload"].SaveAs(fileLocation);
                //    XmlTextReader xmlreader = new XmlTextReader(fileLocation);
                //    // DataSet ds = new DataSet();
                //    ds.ReadXml(xmlreader);
                //    xmlreader.Close();
                //}
                //for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                //{
                //    string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                //    SqlConnection con = new SqlConnection(conn);
                //    string query = "Insert into Person(Name,Email,Mobile) Values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "')";
                //    con.Open();
                //    SqlCommand cmd = new SqlCommand(query, con);
                //    cmd.ExecuteNonQuery();
                //    con.Close();
                //}
            }
            return View();
        }

        public ActionResult ImportexcelOverview()
        {


            DataSet ds = new DataSet();
            if (Request.Files["FileUpload2"].ContentLength > 0)
            {
                string fileExtension =
                                     System.IO.Path.GetExtension(Request.Files["FileUpload2"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload2"].FileName;

                    //if (System.IO.File.Exists(fileLocation))
                    //{
                    //    System.IO.File.Delete(fileLocation);
                    //}

                    Request.Files["FileUpload2"].SaveAs(fileLocation);

                    string excelConnectionString = string.Empty;
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();
                    DataTable dt = new DataTable();

                    dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return null;
                    }
                    String[] excelSheets = new String[dt.Rows.Count];
                    int t = 0;
                    //excel data saves in temp file here.
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[t] = row["TABLE_NAME"].ToString();
                        t++;
                    }
                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                    string query = string.Format("Select * from [{0}]", "Overview$");
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                    {
                        dataAdapter.Fill(ds);
                    }
                }

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String codeStock = ds.Tables[0].Rows[i][0].ToString();
                   // String value = ds.Tables[0].Rows[i][1].ToString();
                   // String qarter = ds.Tables[0].Rows[i][2].ToString();
                    String overView = ds.Tables[0].Rows[i][3].ToString();
                   // String type = ds.Tables[0].Rows[i][4].ToString();

                    String abc = dao.Select(codeStock);
                    if (!"".Equals(abc))
                    {
                        //if ("".Equals(value))
                        //{
                        //    value = "0";
                        //}
                        dao.InsertOverView(abc, overView);

                        Console.WriteLine("insert ok for " + i + " of ds.Tables[0].Rows.Count");
                    }

                }
            }
            return View();
        }

        public ActionResult ImportexcelSnapshot()
        {


            DataSet ds = new DataSet();
            if (Request.Files["FileUpload3"].ContentLength > 0)
            {
                string fileExtension =
                                     System.IO.Path.GetExtension(Request.Files["FileUpload3"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")
                {
                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload3"].FileName;

                    //if (System.IO.File.Exists(fileLocation))
                    //{
                    //    System.IO.File.Delete(fileLocation);
                    //}

                    Request.Files["FileUpload3"].SaveAs(fileLocation);

                    string excelConnectionString = string.Empty;
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    //connection String for xls file format.
                    if (fileExtension == ".xls")
                    {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    }
                    //connection String for xlsx file format.
                    else if (fileExtension == ".xlsx")
                    {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    //Create Connection to Excel work book and add oledb namespace
                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                    excelConnection.Open();
                    DataTable dt = new DataTable();

                    dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return null;
                    }
                    String[] excelSheets = new String[dt.Rows.Count];
                    int t = 0;
                    //excel data saves in temp file here.
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[t] = row["TABLE_NAME"].ToString();
                        t++;
                    }
                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                    string query = string.Format("Select * from [{0}]", "Snapshot$");
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                    {
                        dataAdapter.Fill(ds);
                    }
                }

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String codeStock = ds.Tables[0].Rows[i][0].ToString();
                    String abc = dao.Select(codeStock);

                    if (!"".Equals(abc))
                    {
                        //if ("".Equals(value))
                        //{
                        //    value = "0";
                        //}

                        //Giá thấp nhất 52 tuần
                        String lowest_in_52_weeks = ds.Tables[0].Rows[i][1].ToString();
                        lowest_in_52_weeks = lowest_in_52_weeks.Replace(",", "");
                        Decimal p2 = 0;
                        if (!"".Equals(lowest_in_52_weeks))
                        {
                            p2 = Decimal.Parse(lowest_in_52_weeks);
                        }

                        // Giá cao nhất 52 tuần
                        String highest_in_52_weeks = ds.Tables[0].Rows[i][2].ToString();
                        highest_in_52_weeks = highest_in_52_weeks.Replace(",", "");
                        //Decimal p8 = Decimal.Parse(highest_in_52_weeks);
                        Decimal p8 = 0;
                        if (!"".Equals(highest_in_52_weeks))
                        {
                            p8 = Decimal.Parse(highest_in_52_weeks);
                        }

                        // KLGD BQ 10 ngày
                        String average_volume_in_10_days = ds.Tables[0].Rows[i][3].ToString();
                        average_volume_in_10_days = average_volume_in_10_days.Replace(",", "");
                        //int p3 = Int16.Parse(average_volume_in_10_days);
                        int p3 = 0;
                        if (!"".Equals(average_volume_in_10_days))
                        {
                            p3 = int.Parse(average_volume_in_10_days);
                        }

                        // Thị giá vốn
                        String market_capitalization = ds.Tables[0].Rows[i][4].ToString();
                        market_capitalization = market_capitalization.Replace(",", "");
                        //int p5 = Int16.Parse(market_capitalization);
                        Double p5 = 0;
                        if (!"".Equals(market_capitalization))
                        {
                            p5 = Double.Parse(market_capitalization);
                        }


                        // SLCP lưu hành
                        String outstanding_shares = ds.Tables[0].Rows[i][5].ToString();
                        outstanding_shares = outstanding_shares.Replace(",", "");
                        //int p6 = Int16.Parse(outstanding_shares);
                        int p6 = 0;
                        if (!"".Equals(outstanding_shares))
                        {
                            p6 = int.Parse(outstanding_shares);
                        }

                        // SLCP niêm yết
                        String listed_shares = ds.Tables[0].Rows[i][6].ToString();
                        listed_shares = listed_shares.Replace(",", "");
                        //int p7 = Int16.Parse(listed_shares);
                        int p7 = 0;
                        if (!"".Equals(listed_shares))
                        {
                            p7 = int.Parse(listed_shares);
                        }

                        // Tỷ lệ cổ tức/thị giá
                        String dividend_yield = ds.Tables[0].Rows[i][7].ToString();
                        dividend_yield = dividend_yield.Replace(",", "");
                        //Decimal p4 = Decimal.Parse(dividend_yield);
                        Decimal p4 = 0;
                        if (!"".Equals(dividend_yield))
                        {
                            p2 = Decimal.Parse(dividend_yield);
                        }

                        // EPS 4Q
                        String eps_4q = ds.Tables[0].Rows[i][8].ToString();
                        eps_4q = eps_4q.Replace(",", "");
                        //int p9 = Int16.Parse(eps_4q);
                        int p9 = 0;
                        //if (!"".Equals(eps_4q))
                        //{
                        //    p9 = int.Parse(eps_4q);
                        //}

                        // ROA trailing
                        String roa_trailing = ds.Tables[0].Rows[i][9].ToString();
                        roa_trailing = roa_trailing.Replace(",", "");
                        roa_trailing = roa_trailing.Replace("%", "");
                        //Decimal p10 = Decimal.Parse(roa_trailing);
                        Decimal p10 = 0;
                        if (!"".Equals(roa_trailing))
                        {
                            p10 = Decimal.Parse(roa_trailing);
                        }

                        // ROE trailing
                        String roe_trailing = ds.Tables[0].Rows[i][10].ToString();
                        roe_trailing = roe_trailing.Replace(",", "");
                        //Decimal p13 = Decimal.Parse(roe_trailing);
                        Decimal p13 = 0;
                        if (!"".Equals(roa_trailing))
                        {
                            p13 = Decimal.Parse(roa_trailing);
                        }


                        // P/E trailing
                        String pe_trailing = ds.Tables[0].Rows[i][11].ToString();
                        pe_trailing = pe_trailing.Replace(",", "");
                        //Double p11 = Double.Parse(pe_trailing);
                        Double p11 = 0;
                        if (!"".Equals(pe_trailing))
                        {
                            p11 = Double.Parse(pe_trailing);
                        }

                        // P/B
                        String pb = ds.Tables[0].Rows[i][12].ToString();
                        pb = pb.Replace(",", "");
                        //Double p12 = Double.Parse(pb);
                        Double p12 = 0;
                        if (!"".Equals(pb))
                        {
                            p12 = Double.Parse(pb);
                        }

                        dao.InsertSnapShot(abc, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13);

                        Console.WriteLine("insert ok for " + i + " of ds.Tables[0].Rows.Count");
                    }


                    // String value = ds.Tables[0].Rows[i][1].ToString();
                    // String qarter = ds.Tables[0].Rows[i][2].ToString();
                    String overView = ds.Tables[0].Rows[i][3].ToString();
                    // String type = ds.Tables[0].Rows[i][4].ToString();

                    

                }
            }
            return View();
        }
    }
}
